package qingxin.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

import qingxin.dao.PhotoDAO;
import qingxin.dbc.DatabaseConnection;
import qingxin.vo.Photo;

public class PhotoDAOImpl implements PhotoDAO {

	@Override
	public Photo getByID(int id) throws Exception {
		Photo photo = null;
		String sql = "select pid, p_nid, p_aid, small_nav_addr, " +
				"original_addr, cover_addr, description, avatar_addr, photographer, cwb_addr, horizontal " +
				"from photo where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				photo = new Photo();
				photo.setPhotoID(rs.getInt(1));
				photo.setNewsID(rs.getInt(2));
				photo.setAlbumID(rs.getInt(3));
				
				photo.setSmallNavAddress(rs.getString(4));
				photo.setOriginalAddress(rs.getString(5));
				photo.setCoverAddress(rs.getString(6));
				
				photo.setDescription(rs.getString(7));
				photo.setAvatarAddress(rs.getString(8));
				photo.setPhotographer(rs.getString(9));
				photo.setCwbAddress(rs.getString(10));
				
				photo.setHorizontal(rs.getBoolean(11));
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return photo;
	}

	@Override
	public Photo[] getByNewsID(int newsID) throws Exception {
		ArrayList<Photo> photos = new ArrayList<Photo>();
		String sql = "select pid, p_nid, p_aid, small_nav_addr, " +
				"original_addr, cover_addr,description, avatar_addr, photographer, cwb_addr, horizontal " +
				"from photo where p_nid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, newsID);
			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				Photo photo = new Photo();
				photo.setPhotoID(rs.getInt(1));
				photo.setNewsID(rs.getInt(2));
				photo.setAlbumID(rs.getInt(3));
				
				photo.setSmallNavAddress(rs.getString(4));
				photo.setOriginalAddress(rs.getString(5));
				photo.setCoverAddress(rs.getString(6));
				
				photo.setDescription(rs.getString(7));
				photo.setAvatarAddress(rs.getString(8));
				photo.setPhotographer(rs.getString(9));
				photo.setCwbAddress(rs.getString(10));
				photo.setHorizontal(rs.getBoolean(11));
				photos.add(photo);
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return photos.toArray(new Photo[0]);
	}

	@Override
	public void updateNewsID(int newsID, int photoID) throws Exception {
		String sql = "update photo set p_nid = ? where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			if(newsID == 0){
				pstmt.setNull(1, Types.INTEGER);
			}
			else{
				pstmt.setInt(1, newsID);
			}
			pstmt.setInt(2, photoID);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		
	}

	@Override
	public Photo[] getByAlbumID(int albumID)
			throws Exception {

		ArrayList<Photo> photos = new ArrayList<Photo>();
		String sql = "select pid, p_nid, p_aid, small_nav_addr, " +
				"original_addr, cover_addr,description,avatar_addr, photographer , cwb_addr " +
				"from photo where p_aid = ? order by pid"; 
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, albumID);
			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				Photo photo = new Photo();
				photo.setPhotoID(rs.getInt(1));
				photo.setNewsID(rs.getInt(2));
				photo.setAlbumID(rs.getInt(3));
				
				photo.setSmallNavAddress(rs.getString(4));
				photo.setOriginalAddress(rs.getString(5));
				photo.setCoverAddress(rs.getString(6));
				
				photo.setDescription(rs.getString(7));
				photo.setAvatarAddress(rs.getString(8));
				photo.setPhotographer(rs.getString(9));
				photo.setCwbAddress(rs.getString(10));
				photos.add(photo);
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return photos.toArray(new Photo[0]);
	}

	@Override
	public void updateAlbumID(int photoID, int albumID) throws Exception {
		String sql = "update photo set p_aid = ? where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc .getConnection().prepareStatement(sql);
			
			pstmt.setInt(1, albumID);
			pstmt.setInt(2, photoID);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		
	}

	@Override
	public void addAvatar(Photo photo) throws Exception {
		String sql1 = "select max(pid) from photo";
		String sql2 = "insert into photo (avatar_addr) values (?)";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt1 = dbc.getConnection().prepareStatement(sql1);
			ResultSet rs = pstmt1.executeQuery();
			rs.next();
			int nextID = rs.getInt(1)+1;
			
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setString(1, "/photos/"+nextID+".jpg");
			
			pstmt.executeUpdate();
			
			ResultSet rs2 = pstmt.getGeneratedKeys();
			rs2.next();
			photo.setPhotoID(rs2.getInt(1));
			
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public void addOriginalPhoto(Photo photo) throws Exception {
		String sql1 = "select max(pid) from photo";
		String sql2 = "insert into photo (original_addr) values (?)";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt1 = dbc.getConnection().prepareStatement(sql1);
			ResultSet rs = pstmt1.executeQuery();
			rs.next();
			int nextID = rs.getInt(1)+1;
			
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setString(1, "/photos/"+nextID+".jpg");
			
			pstmt.executeUpdate();
			
			ResultSet rs2 = pstmt.getGeneratedKeys();
			rs2.next();
			photo.setPhotoID(rs2.getInt(1));
			
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	
	}


	@Override
	public void addNewsPhoto(Photo photo) throws Exception {
		String sql1 = "select max(pid) from photo";
		String sql2 = "insert into photo (original_addr, small_nav_addr) values (?,?)";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt1 = dbc.getConnection().prepareStatement(sql1);
			ResultSet rs = pstmt1.executeQuery();
			rs.next();
			int nextID = rs.getInt(1)+1;
			
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setString(1, "/photos/"+nextID+"_original.jpg");
			pstmt.setString(2, "/photos/"+nextID+"_small.jpg");
			
			pstmt.executeUpdate();
			
			ResultSet rs2 = pstmt.getGeneratedKeys();
			rs2.next();
			photo.setPhotoID(rs2.getInt(1));
			
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		
		
	}

	@Override
	public void addCwbPhoto(Photo photo) throws Exception {
		String sql1 = "select max(pid) from photo";
		String sql2 = "insert into photo (cwb_addr) values (?)";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt1 = dbc.getConnection().prepareStatement(sql1);
			ResultSet rs = pstmt1.executeQuery();
			rs.next();
			int nextID = rs.getInt(1)+1;
			
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setString(1, "/photos/"+nextID+".jpg");
			
			pstmt.executeUpdate();
			
			ResultSet rs2 = pstmt.getGeneratedKeys();
			rs2.next();
			photo.setPhotoID(rs2.getInt(1));
			
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public void updateDescription(String description, int photoID) throws Exception {
		String sql = "update photo set description = ? where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc .getConnection().prepareStatement(sql);
			
			pstmt.setString(1, description);
			pstmt.setInt(2, photoID);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public void updateHorizontl(boolean horizontal, int photoID)
			throws Exception {
		String sql = "update photo set horizontal = ? where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc .getConnection().prepareStatement(sql);
			
			pstmt.setBoolean(1, horizontal);
			pstmt.setInt(2, photoID);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public void updateCwb(String cwb, int photoID) throws Exception {
		String sql = "update photo set cwb_addr = ? where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc .getConnection().prepareStatement(sql);
			
			pstmt.setString(1,cwb);
			pstmt.setInt(2, photoID);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		
	}

	@Override
	public void updatePhotographer(String photographer, int photoID)
			throws Exception {
		String sql = "update photo set photographer = ? where pid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc .getConnection().prepareStatement(sql);
			
			pstmt.setString(1,photographer);
			pstmt.setInt(2, photoID);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	
}
